﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for DABasic
/// </summary>
public class DABasic
{
    SqlConnection _connection;
    public DABasic()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    private void OpenConnection()
    {
        _connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["sqlString"].ConnectionString);
        if (_connection.State == System.Data.ConnectionState.Open)
        {
            _connection.Close();
            _connection.Open();
        }
        else
        {
            _connection.Open();
        }
    }

    private void CloseConnection()
    {
        if (_connection != null)
        {
            _connection.Close();
            _connection.Dispose();
        }
    }

    public DataSet ExecuteGetDataSet(string _comand, SqlParameter[] _para, CommandType _type)
    {
        OpenConnection();
        #region command
        SqlCommand _command = new SqlCommand();
        _command.Connection = _connection;
        _command.CommandText = _comand;
        _command.CommandType = _type;
        _command.Parameters.AddRange(_para);
        #endregion
        #region adapter
        SqlDataAdapter _adapter = new SqlDataAdapter();
        _adapter.SelectCommand = _command;
        #endregion
        #region dataSet
        DataSet _dataset = new DataSet();
        _command.ExecuteNonQuery();
        _adapter.Fill(_dataset);
        #endregion
        CloseConnection();
        return _dataset;
    }

    public SqlParameterCollection ExecuteReturnParameters(string _comand, SqlParameter[] _para, CommandType _type)
    {
        OpenConnection();
        #region command
        SqlCommand _command = new SqlCommand();
        _command.Connection = _connection;
        _command.CommandText = _comand;
        _command.CommandType = _type;
        _command.Parameters.AddRange(_para);
        #endregion

        _command.ExecuteNonQuery();
        SqlParameterCollection output = _command.Parameters;

        CloseConnection();

        return output;
    }

    public List<object> ExecuteReturnBoth(string _comand, SqlParameter[] _para, CommandType _type)
    {
        OpenConnection();
        #region command
        SqlCommand _command = new SqlCommand();
        _command.Connection = _connection;
        _command.CommandText = _comand;
        _command.CommandType = _type;
        _command.Parameters.AddRange(_para);
        #endregion

        _command.ExecuteNonQuery();
        #region adapter
        SqlDataAdapter _adapter = new SqlDataAdapter();
        _adapter.SelectCommand = _command;
        #endregion
        #region dataSet
        DataSet _dataset = new DataSet();
        _command.ExecuteNonQuery();
        _adapter.Fill(_dataset);
        #endregion
        SqlParameterCollection _parameters = _command.Parameters;

        CloseConnection();

        List<object> output = new List<object>();
        output.Add(_parameters);
        output.Add(_dataset);
        return output;
    }
}
